package template;

import com.sun.webkit.Timer;
import org.springframework.context.ApplicationContext;
import org.springframework.context.support.ClassPathXmlApplicationContext;
import org.springframework.jdbc.core.BatchPreparedStatementSetter;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.PreparedStatementCreator;
import org.springframework.jdbc.core.RowCallbackHandler;
import org.springframework.jdbc.datasource.DataSourceUtils;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.jdbc.support.KeyHolder;

import java.sql.*;
import java.time.LocalTime;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.List;
import java.util.Random;

public class TemplateTest {

    public static void main(String[] args) {

        ApplicationContext context = new ClassPathXmlApplicationContext("template.xml");
        JdbcTemplate template = context.getBean("jdbcTemplate", JdbcTemplate.class);

//        DriverManagerDataSource ds = new DriverManagerDataSource();
//        ds.setDriverClassName("com.mysql.jdbc.Driver");
//        ds.setUrl("jdbc:mysql://127.0.0.1:3306/employees?useSSL=false");
//        ds.setUsername("root");
//        ds.setPassword("123456");
//

        final String sql = "CREATE TABLE IF NOT EXISTS t_user (user_id INT PRIMARY KEY, user_name VARCHAR(60))";
        template.execute(sql);

        final String insertSql = "INSERT INTO t_user (user_id, user_name) VALUES (?, 'chengxq')";

        template.update(con -> {

            PreparedStatement statement = con.prepareStatement(insertSql);
            statement.setInt(1,
                    LocalTime.now().getSecond()
            );

            return statement;
        });

        String updateSql = "UPDATE t_user SET user_name=? WHERE user_id=1";
        template.update(updateSql, new Object[]{"chengxq" + new Random().nextBoolean()});

        String insertSql2 = "INSERT INTO t_user (user_name) VALUES ('chengxq')";

        Connection connection = DataSourceUtils.getConnection(template.getDataSource());
        try {
            // insert之后返回主键
            Statement statement = connection.createStatement();
            statement.executeUpdate(insertSql2, Statement.RETURN_GENERATED_KEYS);
            ResultSet rs = statement.getGeneratedKeys();
            if (rs.next()) {
                System.out.println(rs.getInt(1));
            }

            // template 执行insert返回主键
            KeyHolder keyHolder = new GeneratedKeyHolder();
            template.update(con -> {
                PreparedStatement preparedStatement = con.prepareStatement(insertSql2, Statement.RETURN_GENERATED_KEYS);
                return preparedStatement;
            }, keyHolder);
            System.out.println(keyHolder.getKey());
            String insertSql3 = "INSERT INTO t_user (user_name) VALUES (?)";
            List list = Arrays.asList("chengxq2", "chengxq3", "chengxq4");
            template.batchUpdate(insertSql3, new BatchPreparedStatementSetter() {

                @Override
                public void setValues(PreparedStatement ps, int i) throws SQLException {
                    String uname = String.class.cast(list.get(i));
                    ps.setString(1, uname);
                }

                @Override
                public int getBatchSize() {
                    return list.size();
                }
            });

            // 查询数据
            String querySql = "SELECT * FROM t_user";
            List<String> uname = new ArrayList<>();
            template.query(querySql, rs1 -> {
                uname.add(rs1.getString("user_name"));
            });
            System.out.println("uname:" + uname);
            // 查询单值
            String countQuery = "SELECT COUNT(*) FROM t_user";
            int count = template.queryForObject(countQuery, Integer.class);
            System.out.println("count:" + count);
        } catch (SQLException e) {
            e.printStackTrace();
        }

    }

}
